package com.example.fangyimanagement;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.hutool.core.io.FastByteArrayOutputStream;
import cn.hutool.core.io.IoUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.example.fangyimanagement.core.BizException;
import com.example.fangyimanagement.core.LoginUser;
import com.example.fangyimanagement.core.Result;
import com.example.fangyimanagement.core.StringUtils;
import com.example.fangyimanagement.data.Bingli;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;


@RestController
@RequestMapping("/bingliManage")
public class bingliManageController {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @RequestMapping("/addBingli")
    public Result<Void> addBingli(@RequestBody Map<String, String> param) {
        String sfz = param.get("sfz");
        String name = param.get("name");
        String districtCode = param.get("districtCode");
        String gender = param.get("gender");
        String age = param.get("age");
        String address = param.get("address");
        String status = param.get("status");
        String remark = param.get("remark");
        //        操作数据库
        String sql = "insert into bingli_data (bl_sfz, bl_name, district_code, gender, age, address, status, remark, create_time) " +
                "values (?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIME)";
        int rows = this.jdbcTemplate.update(sql, sfz, name, districtCode, gender, age, address, status, remark);
        if (rows > 0) {
            return Result.success();
        } else {
            return Result.failed("添加失败");
        }
    }
    @RequestMapping("/deleteBingli")
    public Result<Void> deleteBingli(@RequestBody Map<String, String> param) {
        String id = param.get("id");
        //        操作数据库
        int rows = this.jdbcTemplate.update("delete from bingli_data where id =?", id);
        if (rows > 0) {
            return Result.success();
        } else {
            return Result.failed("删除失败");
        }
    }
    @RequestMapping("/updateBingli")
    public Result<Void> updateBingli(@RequestBody Map<String, String> param) {
        String id = param.get("id");
        String sfz = param.get("sfz");
        String name = param.get("name");
        String districtCode = param.get("districtCode");
        String gender = param.get("gender");
        String age = param.get("age");
        String address = param.get("address");
        String status = param.get("status");
        String remark = param.get("remark");
        //        操作数据库
        String sql = "update bingli_data set bl_sfz=?, bl_name=?, district_code=?, gender=?, age=?, address=?, status=?, remark=?, update_time=NOW() where id =?";
        int rows = this.jdbcTemplate.update(sql, sfz, name, districtCode, gender, age, address, status, remark, id);
        if (rows > 0) {
            return Result.success();
        } else {
            return Result.failed("编辑失败");
        }
    }
    @RequestMapping("/bingliList")
    public Result<List<Map<String, Object>>> bingliList(@RequestBody Map<String, String> param) {
        String name = param.get("name");
        String sfz = param.get("sfz");
        String districtCode = param.get("districtCode");
        String status = param.get("status");
        String sql = "SELECT bingli_data.id, bl_name AS blName, bl_sfz AS blSfz, gender, age, address, status, " +
                "bingli_data.remark, bingli_data.create_time AS createTime, bingli_data.update_time AS updateTime, \n" +
                "       comm_dict.name AS statusName, " +
                "bingli_data.district_code AS districtCode, glpt_org.org_name as districtName " +
                "FROM bingli_data \n" +
                "JOIN glpt_org ON bingli_data.district_code = glpt_org.org_code \n" +
                "JOIN comm_dict ON bingli_data.status = comm_dict.code \n" +
                "WHERE 1=1";
        if (name != null && !name.isEmpty()) {
            sql = sql + " AND bl_name like '%" + name + "%'";
        }
        if (sfz != null && !sfz.isEmpty()) {
            sql = sql + " AND bl_sfz like '%" + sfz + "%'";
        }
        if (districtCode != null && !districtCode.isEmpty()) {
            sql = sql + " AND district_code = '" + districtCode + "'";
        }
        if (status != null && !status.isEmpty()) {
            sql = sql + " AND status = '" + status + "'";
        }
        sql = sql + " ORDER BY id";
        List<Map<String, Object>> orgList = this.jdbcTemplate.queryForList(sql);
        return Result.success(orgList);
    }

    /**
     * 获取南京下面所有的区的列表
     * @param param
     * @return
     */
    @RequestMapping("/getMyDistrictList")
    public Result<List<Map<String, Object>>> getMyDistrictList(@RequestBody Map<String, String> param, LoginUser loginUser) {
        String account = loginUser.getAccount();
        List<Map<String, Object>> accountRoleOrgList = this.jdbcTemplate.queryForList("SELECT name, role_code, org_code FROM glpt_user WHERE account=?", account);
        /**
         * 根据登录人账号找到了角色和组织编码，查数据时就要做权限控制
         */
        if (accountRoleOrgList.size() > 0) {
            String accountRole = (String) accountRoleOrgList.get(0).get("role_code");
            String accountOrg = (String) accountRoleOrgList.get(0).get("org_code");
            String sql = "SELECT org_code AS code, org_name AS name FROM glpt_org WHERE parent_org_code = '025'";
            List<Object> paramList = new ArrayList<>();
            if ((accountRole.equals("district_manager") || accountRole.equals("hsjcdManager")) && accountOrg != null) {
                sql = sql + " AND org_code = ?";
                paramList.add(accountOrg);
            }
            List<Map<String, Object>> districtList = this.jdbcTemplate.queryForList(sql, paramList.toArray());
            return Result.success(districtList);
        } else {
            return Result.failed("查询失败，请稍后再试");
        }
    }

    /**
     * 导出病例人员列表
     * @param param
     * @param response
     * @return
     */
    @RequestMapping("/exportBingliList")
    public Result<Void> exportBingliList(@RequestBody Map<String, String> param, HttpServletResponse response) {
        String name = param.get("name");

        List<Object> paramList = new ArrayList<>();
        String sql = "SELECT * FROM bingli_data WHERE bl_name LIKE '%" + name + "%' ";
        sql = sql + " ORDER BY id";
        //         设置响应头信息
        response.setContentType("application/vnd.ms-excel");
        //设置编码格式
        response.setCharacterEncoding("UTF-8");
        //设置导出文件名称（避免乱码）
        // 设置响应头
        response.setHeader("Content-disposition", "attachment; filename=huanzhe.xlsx");
        // Map作为每一行的数据容器，List作为行的容器
        List<Bingli> bingliList = this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Bingli.class), paramList.toArray());

//            后端日志输出病例列表
        System.out.println(bingliList.size() + "条数据需要导出，以下是此次导出成功的病例姓名：");
        for (int i = 0; i < bingliList.size(); i++) {
            System.out.println(bingliList.get(i).getBingliName());
        }
        // 生成workbook 并导出
        try (ServletOutputStream outputStream = response.getOutputStream()) {
            EasyExcel.write(outputStream)
                    .sheet("病例")
                    .head(Bingli.class)
                    .doWrite(bingliList);
            return null;
        } catch (Exception e) {
            throw new BizException("导出失败", e);
        }
    }

    /**
     * 导入病例信息
     *
     * @param file
     * @param response
     * @return
     */
    @RequestMapping("/importBingliList")
    public Result<Void> importBingliList(MultipartFile file, HttpServletResponse response) {
        try (InputStream inputStream = file.getInputStream()) {
            FastByteArrayOutputStream fbaos = IoUtil.read(inputStream);
            byte[] bytes = fbaos.toByteArray();
            List<Bingli> bingliList = EasyExcel.read(new ByteArrayInputStream(bytes))
                    .head(Bingli.class)
                    .sheet(0)
                    .doReadSync();

            if (bingliList.isEmpty()) {
                throw new BizException("要导入的数据为空");
            }
            List<Map<String, Object>> dictList = this.jdbcTemplate.queryForList("SELECT code, name FROM comm_dict WHERE type = 'status'");
            Map<String, String> zzStatus = new HashMap<>();
            for (int i = 0; i < dictList.size(); i++) {
                zzStatus.put((String) dictList.get(i).get("name"), (String) dictList.get(i).get("code"));
            }
            System.out.println(zzStatus.get("wzz"));
            int[] rows = this.namedParameterJdbcTemplate.batchUpdate(
                    "INSERT INTO bingli_data(\n" +
                            "    bl_sfz, bl_name, district_code, gender, age, address, status, remark, create_time, update_time\n" +
                            ") VALUES (\n" +
                            "    :bingliSfz, :bingliName, :districtCode, :gender, :age, :address, :status, :remark, :createTime, :updateTime\n" +
                            ")\n" +
                            "ON DUPLICATE KEY UPDATE\n" +
                            "                     bl_sfz = :bingliSfz,\n" +
                            "                     bl_name = :bingliName,\n" +
                            "                     district_code = :districtCode,\n" +
                            "                     gender = :gender,\n" +
                            "                     age = :age,\n" +
                            "                     address = :address,\n" +
                            "                     status = :districtCode,\n" +
                            "                     remark = :remark,\n" +
                            "                     create_time = :createTime,\n" +
                            "                     update_time = :updateTime\n" +
                            "                     ",
                    bingliList.stream().map(BeanPropertySqlParameterSource::new)
                            .toArray(BeanPropertySqlParameterSource[]::new));
            return Result.success().setMsg(StringUtils.format("成功导入{}条数据", rows.length));
        } catch (IOException e) {
            throw new BizException("导入失败", e);
        }
    }

    /**
     * 获取指定时间段的新增阳性病例总数据
     * @param param
     * @return
     */
    @RequestMapping("/getYangTotal")
    public Result<Integer> getYangTotal(@RequestBody Map<String, String> param) {
        String timeStart = param.get("timeStart");
        String timeEnd = param.get("timeEnd");
        List<Object> paramList = new ArrayList<>();
        String sql = "SELECT COUNT(1) AS total FROM hsjc_result WHERE hsjc_result.jc_result = 'yang'";
        if (timeStart != null && !timeStart.isEmpty()) {
            sql = sql + " AND hsjc_result.report_time >= ?";
            paramList.add(timeStart);
        }
        if (timeEnd != null && !timeEnd.isEmpty()) {
            sql = sql + " AND hsjc_result.report_time <= ?";
            paramList.add(timeEnd);
        }
        Integer count  = this.jdbcTemplate.queryForObject(sql, Integer.class, paramList.toArray());
        return Result.success(count);
    }
    /**
     * 按行政区查询总的核酸检测结果数据，而且只查询阳性
     * @param param
     * @return
     */
    @RequestMapping("/getYangListByDistrict")
    public Result<List<Map<String, Object>>> getYangListByDistrict(@RequestBody Map<String, String> param) {
        String timeStart = param.get("timeStart");
        String timeEnd = param.get("timeEnd");
        List<Object> paramList = new ArrayList<>();
        String sql = "SELECT district_code as districtCode, glpt_org.org_name AS districtName, \n" +
                "       COUNT(1) AS num FROM hsjc_result" +
                " \n" +
                "        JOIN glpt_org ON glpt_org.org_code = hsjc_result.district_code" +
                " \n" +
                "        WHERE hsjc_result.jc_result = 'yang'\n" +
                "        \n" +
                "        \n" +
                "        ";
        if (timeStart != null && !timeStart.isEmpty()) {
            sql = sql + " AND hsjc_result.report_time >= ?";
            paramList.add(timeStart);
        }
        if (timeEnd != null && !timeEnd.isEmpty()) {
            sql = sql + " AND hsjc_result.report_time <= ?";
            paramList.add(timeEnd);
        }
        sql = sql + " GROUP BY hsjc_result.district_code, glpt_org.org_name";
                List<Map<String, Object>> yesterdayData = this.jdbcTemplate.queryForList(sql, paramList.toArray());
        return Result.success(yesterdayData);
    }

    /**
     * 按症状类型（无症状、轻症、重症、死亡）分类查询
     * @param param
     * @return
     */
    @RequestMapping("/getYangListByStatus")
    public Result<List<Map<String, Object>>> getYangListByStatus(@RequestBody Map<String, String> param) {
        String timeStart = param.get("timeStart");
        String timeEnd = param.get("timeEnd");
        List<Object> paramList = new ArrayList<>();
        String sql = "SELECT zz_type AS type, comm_dict.name, COUNT(1) AS num FROM hsjc_result \n" +
                "JOIN comm_dict on comm_dict.code = hsjc_result.zz_type \n" +
                "WHERE hsjc_result.jc_result = 'yang' \n" +
                "\n" +
                "\n";
        if (timeStart != null && !timeStart.isEmpty()) {
            sql = sql + " AND hsjc_result.report_time >= ?";
            paramList.add(timeStart);
        }
        if (timeEnd != null && !timeEnd.isEmpty()) {
            sql = sql + " AND hsjc_result.report_time <= ?";
            paramList.add(timeEnd);
        }
        sql = sql + " GROUP BY hsjc_result.zz_type, comm_dict.name";
        List<Map<String, Object>> zzList = this.jdbcTemplate.queryForList(sql, paramList.toArray());
        return Result.success(zzList);
    }

    /**
     * 查询最近7天的阳性病例数走势图，按部门分类
     * @param param
     * @return
     */
    @RequestMapping("/getPrevWeekYangListByDistrict")
    public Result<List<Map<String, Object>>> getPrevWeekYangListByDistrict(@RequestBody Map<String, String> param) {
        String timeStart = param.get("timeStart");
        String timeEnd = param.get("timeEnd");
        List<Object> paramList = new ArrayList<>();
        String sql = "SELECT district_code AS code, glpt_org.org_name AS name, \n" +
                "DATE_FORMAT(report_time,'%Y-%m-%d') AS date, \n" +
                "       COUNT(1) AS num FROM hsjc_result" +
                " \n" +
                "           JOIN glpt_org ON glpt_org.org_code = hsjc_result.distri" +
                "ct_code \n" +
                "WHERE hsjc_result.jc_result = 'yang' ";
        if (timeStart != null && !timeStart.isEmpty()) {
            sql = sql + " AND hsjc_result.report_time >= ?";
            paramList.add(timeStart);
        }
        if (timeEnd != null && !timeEnd.isEmpty()) {
            sql = sql + " AND hsjc_result.report_time <= ?";
            paramList.add(timeEnd);
        }
        sql = sql + " GROUP BY hsjc_result.district_code, glpt_org.org_name, DATE_FORMAT(report_time,'%Y-%m-%d')";
        List<Map<String, Object>> yesterdayData = this.jdbcTemplate.queryForList(sql, paramList.toArray());
        return Result.success(yesterdayData);
    }

    /**
     * 查询最近7天的阳性病例数走势图，按症状类型分类
     * @param param
     * @return
     */
    @RequestMapping("/getPrevWeekYangListByStatus")
    public Result<List<Map<String, Object>>> getPrevWeekYangListByStatus(@RequestBody Map<String, String> param) {
        String timeStart = param.get("timeStart");
        String timeEnd = param.get("timeEnd");
        List<Object> paramList = new ArrayList<>();
        String sql = "SELECT hsjc_result.zz_type AS code, comm_dict.name, DATE_FORMAT(report_time,'%Y-%m-%d') AS date, COUNT(1) AS num FROM hsjc_result" +
                " JOIN comm_dict ON comm_dict.code = hsjc_result.zz_type" +
                " WHERE hsjc_result.jc_result = 'yang'";
        if (timeStart != null && !timeStart.isEmpty()) {
            sql = sql + " AND hsjc_result.report_time >= ?";
            paramList.add(timeStart);
        }
        if (timeEnd != null && !timeEnd.isEmpty()) {
            sql = sql + " AND hsjc_result.report_time <= ?";
            paramList.add(timeEnd);
        }
        sql = sql + " GROUP BY hsjc_result.zz_type, comm_dict.name, DATE_FORMAT(report_time,'%Y-%m-%d')";
        List<Map<String, Object>> yesterdayData = this.jdbcTemplate.queryForList(sql, paramList.toArray());
        return Result.success(yesterdayData);
    }

}
